package com.apes.hr.base.repository;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.hr.base.model.Employee;

import com.apes.hr.base.model.WorkPost;
import com.apes.scm.masterdata.enumeration.model.Enumeration;
import com.apes.scm.masterdata.frame.model.Branch;
import com.apes.scm.masterdata.frame.model.Store;
import com.apes.scm.masterdata.party.model.Party;
import com.apes.scm.masterdata.party.model.PartyRole;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;


import java.util.Date;
import java.util.List;
import java.util.Map;


/**
 * @author HuJianLin
 * @create 2018-05-09 16:07
 */
public interface EmployeeRepository  extends CustomRepository<Employee, String>{

    List<Employee> findAllById(String id);

    List<Employee> findByLastUpdatedStampGreaterThanOrderByLastUpdatedStampDesc(Date lastTimestamp);

    @Query("select T from Employee T where  (id like %:value% or name like %:value%) and (employeeState.id = :type or employeeState is null ) ")
    Page<Employee> findByApplyJob(@Param("value") String value, @Param("type") String type, Pageable pageable);

    @Query("select T from Employee T where employeeState.id = :type and origin is not null  AND company in (:company) AND workBranch =:branch and party_id is not null and (id like %:value%  or name like %:value%) ")
    Page<Employee> findByEmployeeState(@Param("value") String value, @Param("type") String type, @Param("company")List<PartyRole> company, @Param("branch")Branch branch, Pageable pageable);

    @Query("select T from Employee T where 1 = 1 and (id like %:value% or name like %:value%) ")
    Page<Employee> find(@Param("value") String value, Pageable pageable);

    @Query("select T from Employee T where employeeState.id = :employeeState AND company in (:company) and (id like %:value% or name like %:value%)  ")
    Page<Employee> findByEmployeeState2(@Param("value") String value, @Param("employeeState")String type,@Param("company")List<PartyRole> company, Pageable pageable);

    Employee findByIdCard(String idCard);

    List<Employee> findByMobileNumber(String mobileNumber);

    List<Employee> findByName(String name);

    @Query("select T from Employee T where 1 = 1 and (id like %:value% or name like %:value%)")
    List<Employee> findByLikeName(@Param("value")String name);

    Employee findByOrigin(String origin);

    @Query(value = "select t.* from hr_employee t WHERE t.origin = :origin and t.employee_state_id = '000088'",nativeQuery = true)
    Employee findByOriginAndEmployeeState(@Param("origin")String origin);

    @Query(value = "SELECT t.* from hr_employee t WHERE t.employee_state_id = '000088'" +
            "AND( t.on_duty_state IN ('000394','000118') OR (t.job_id = '0004' AND t.rank_id ='004' ))",nativeQuery = true)
    List<Employee> findOnDutyStateAndPostDescribe();

    List<Employee> findByEmployeeState(Enumeration enumeration);

    @Query(value = "select T from Employee T where T.employeeState.id  = '000088' AND T.workBranch.id in ?1")
    Page<Employee> findByWorkBranch(String branch,Pageable pageable);

    @Query(value = "SELECT T.* FROM HR_EMPLOYEE T WHERE T.EMPLOYEE_STATE_ID = '000088' AND t.state!='delete'  AND t.work_branch_id in ?1",nativeQuery = true)
    List<Employee> findByWorkBranch(Branch branch);

    Employee findByParty(Party party);

    @Query(value = "SELECT T.* FROM HR_EMPLOYEE T WHERE T.EMPLOYEE_STATE_ID = '000088' AND t.state!='delete'  AND t.work_branch_id in ?1 and t.post_id in ?2",nativeQuery = true)
    List<Employee> findByWorkBranchAndPost(Branch branch, WorkPost workPost);
    @Query(value = "SELECT T.* FROM HR_EMPLOYEE T WHERE T.EMPLOYEE_STATE_ID = '000088'AND t.state!='delete'  AND t.post_id in?1 and t.on_duty_state=?2",nativeQuery = true)
    List<Employee> findByWorkPostAndOnDutyState(List<WorkPost> workPosts,Enumeration onDutyState);

    @Query(value = "SELECT t.*  FROM hr_employee t WHERE t.party_id = (SELECT p.party_id from mas_person p WHERE p.ding_ding_user_id = :userId ) ",nativeQuery = true)
    Employee findByDingUserId(@Param("userId") String userId);


    @Query("select s " +
            "from Employee s where s.lastUpdatedStamp > :times or (s.id > :id and s.lastUpdatedStamp = :times)  " +
            " order by s.lastUpdatedStamp, s.id asc ")
    List<Employee> findAllEmployeeByTs(@Param("times") Date times, @Param("id") String id, Pageable pageable);

    @Query("select s " +
            "from Employee s where to_char(s.lastUpdatedStamp,'yyyy-mm-dd hh24:mi:ss.ff3' ) > :times or (s.id > :id and to_char(s.lastUpdatedStamp,'yyyy-mm-dd hh24:mi:ss.ff3' ) = :times)  " +
            " order by s.lastUpdatedStamp, s.id asc ")
    List<Employee> findAllEmployeeByTs(@Param("times") String times, @Param("id") String id, Pageable pageable);

    Employee findByIdOrName(String id,String name);

    @Query(value = "SELECT T.* FROM HR_EMPLOYEE T WHERE T.EMPLOYEE_STATE_ID = '000088' AND t.state!='delete'  AND t.work_branch_id = ?1 and t.post_id = ?2",nativeQuery = true)
    List<Employee> findByWorkBranchIdAndPostId(@Param("branchId") String branchId , @Param("postId") String postId);

    //查询在职状态 '000119','000484','000418'
    @Query(value = "SELECT * FROM hr_employee t WHERE t.employee_state_id = '000088' and t.on_duty_state in(:ids) and t.work_branch_id = :branchId and t.post_id = :postId ",nativeQuery = true)
    List<Employee> findByWorkBranchAndPostAndOnDutyState(@Param("ids") List ids,@Param("branchId") String branchId , @Param("postId") String postId);

    @Query(value = "SELECT * FROM hr_employee t WHERE t.employee_state_id = '000088' and t.work_branch_id = :branchId and t.post_id = :postId and t.job_id = :jobId ",nativeQuery = true)
    List<Employee> findByWorkBranchAndPostAndJob(@Param("branchId") String branchId , @Param("postId") String postId,@Param("jobId") String jobId);

    @Query(value = "SELECT * FROM hr_employee t WHERE t.employee_state_id = '000088' and t.work_branch_id = :branchId and " +
            "((t.job_id = :jobId and t.rank_id = :rank_id) or (t.job_id = :jobId and t.rank_id = :rankId2))",nativeQuery = true)
    List<Employee> findByWorkBranchAndJobAndRank(@Param("branchId") String branchId , @Param("jobId") String jobId,@Param("rankId") String rankId,@Param("rankId2") String rankId2);

    @Query(value = "SELECT A.id AS id, B.name AS name FROM PartyRole A " +
            "LEFT JOIN Party B ON A.party.id = B.id " +
            "LEFT JOIN Employee C ON B.id = C.party.id " +
            "WHERE A.role.id = 'Employee' AND C.employeeState.id = '000088' AND ( A.id like %:value% or B.name like %:value%)")
    Page<Map> findAllTransPartyRole(Pageable pageable,@Param("value")String value);

    @Query(value = "select t from Employee t where t.post.id in ('0000016','0000752') and t.employeeState = '000088' and t.workBranch.id = :workBranch")
    List<Employee> findByWorkBranchAndPostEQManager(@Param("workBranch")String workBranch);
}
